#!/usr/bin/env python
# -*- coding: utf-8 -*-

from django.db import connection

from web.constants.direction import Direction
from web.dao.base_dao import BaseDao
from web.models import CommodityFutureInfo
from web.models.optimize2_commodity_future_transaction_record import Optimize2CommodityFutureTransactionRecord
from web.util.fee_util import FeeUtil


class Optimize2CommodityFutureTransactionRecordDao(BaseDao):
    """
    Optimize2CommodityFutureTransactionRecord的dao类
    """

    model_class = Optimize2CommodityFutureTransactionRecord

    def find_by_account_name_and_not_close_position(self, account_name:str) -> list[Optimize2CommodityFutureTransactionRecord]:
        """
        根据account_name，查询正在持仓的期货
        """

        with connection.cursor() as cursor:
            sql: str = "select * from optimize2_c_f_transact_record t " \
                        "where t.account_name = '{}' and ((t.direction = 1 and t.sell_date is null and t.sell_price is null and t.sell_lot is null) " \
                         "or (t.direction = -1 and t.buy_date is null and t.buy_price is null and t.buy_lot is null))".format(account_name)
            cursor.execute(sql)
            _list = cursor.fetchall()
            return self.tuple_list_to_optimize2_commodity_future_transaction_record_list(_list)

    def find_current_close_position_by_account_name(self, account_name: str, transaction_date: str, close_position_reason_list: list[str]) -> list[Optimize2CommodityFutureTransactionRecord]:
        """
        根据account_name，查询当天被平仓的期货
        """

        with connection.cursor() as cursor:
            sql: str = "select * from optimize2_c_f_transact_record t " \
                       "where t.account_name = '{}' and ((t.direction = 1 and t.buy_date is not null and t.sell_date = to_date({}, 'yyyy-mm-dd'))" \
                       "or (t.direction = -1 and t.sell_date is not null and t.buy_date = to_date({}, 'yyyy-mm-dd'))) and t.close_position_reason in ({})".format(
                account_name, transaction_date, transaction_date, ",".join(close_position_reason_list))
            cursor.execute(sql)
            _list = cursor.fetchall()
            return self.tuple_list_to_optimize2_commodity_future_transaction_record_list(_list)

    def find_by_account_name_and_date_and_not_close_position_order_by_profit_and_loss_rate_desc(self, account_name: str, date: str) -> list[Optimize2CommodityFutureTransactionRecord]:
        """
        根据账号和日期，查询持仓的期货，并按照收益率降序排列
        """

        with connection.cursor() as cursor:
            sql: str = "select t.* from optimize2_c_f_transact_record t " \
					"join c_f_date_contract_data cfdcd on cfdcd.code=t.code and cfdcd.transaction_date=to_date({}, 'yyyy-mm-dd') " \
					"where t.account_name='{}' and ((t.direction=1 and t.sell_date is null and t.sell_price is null and t.sell_lot is null) or " \
                    "(t.direction=-1 and t.buy_date is null and t.buy_price is null and t.buy_lot is null)) " \
					"order by case when t.direction=1 then (cfdcd.close_price - t.buy_price)/t.buy_price*100 " \
                    "when t.direction=-1 then (t.sell_price - cfdcd.close_price)/t.sell_price*100 end desc".format(date, account_name)
            cursor.execute(sql)
            _list = cursor.fetchall()
            return self.tuple_list_to_optimize2_commodity_future_transaction_record_list(_list)

    def find_not_close_position(self) -> list[Optimize2CommodityFutureTransactionRecord]:
        """
        查询正在持仓的期货
        """

        with connection.cursor() as cursor:
            sql: str = "select * from optimize2_c_f_transact_record t " \
                        "where ((t.direction = 1 and t.sell_date is null and t.sell_price is null and t.sell_lot is null) " \
                         "or (t.direction = -1 and t.buy_date is null and t.buy_price is null and t.buy_lot is null))"
            cursor.execute(sql)
            _list = cursor.fetchall()
            return self.tuple_list_to_optimize2_commodity_future_transaction_record_list(_list)

    def sum_open_commission_by_account_name(self, account_name: str) -> float:
        """
        根据账户名称，计算所有期货（包括正在持仓的和已经平仓的）的open_commission的和
        """

        with connection.cursor() as cursor:
            sql: str = "select COALESCE(sum(t1.open_commission), 0) from optimize2_c_f_transact_record t1 " \
                       "where t1.account_name = '{}'".format(account_name)
            cursor.execute(sql)
            sum_open_commission: float = cursor.fetchone()
            return sum_open_commission[0]

    def sum_close_commission_by_account_name(self, account_name: str) -> float:
        """
        根据账户名称，计算所有期货（包括正在持仓的和已经平仓的）的close_commission的和
        """

        with connection.cursor() as cursor:
            sql: str = "select COALESCE(sum(t1.close_commission), 0) from optimize2_c_f_transact_record t1 " \
                       "where t1.account_name = '{}'".format(account_name)
            cursor.execute(sql)
            sum_close_commission: float = cursor.fetchone()
            return sum_close_commission[0]

    def sum_open_commission_by_account_name_and_date(self, account_name: str, transaction_date: str) -> float:
        """
        根据账户名称和日期，计算某一天的开仓费用（open_commission，包括做多和做空）之和
        """

        with connection.cursor() as cursor:
            sql: str = None
            sql = "select COALESCE(sum(t1.open_commission), 0) from optimize2_c_f_transact_record t1 " \
                  "where t1.account_name = '{}' " \
                  "and ((t1.direction = 1 and t1.buy_date=to_date({}, 'yyyy-mm-dd') and t1.sell_date is null)" \
                  " or (t1.direction = -1 and t1.sell_date=to_date({}, 'yyyy-mm-dd') and t1.buy_date is null))".format(
                account_name, transaction_date, transaction_date)
            cursor.execute(sql)
            sum_open_commission: float = cursor.fetchone()
            return sum_open_commission[0]

    def tuple_list_to_optimize2_commodity_future_transaction_record_list(self, _list) -> list[Optimize2CommodityFutureTransactionRecord]:
        """
        将list[tuple]类型转换为list[Optimize2CommodityFutureTransactionRecord]类型
        """

        if _list is not None and len(_list) > 0:
            optimize2_commodity_future_transaction_record_list: list[Optimize2CommodityFutureTransactionRecord] = list()
            for row in _list:
                optimize2_commodity_future_transaction_record: Optimize2CommodityFutureTransactionRecord = Optimize2CommodityFutureTransactionRecord()
                optimize2_commodity_future_transaction_record.id_ = row[0]
                optimize2_commodity_future_transaction_record.account_name = row[1]
                optimize2_commodity_future_transaction_record.code = row[2]
                optimize2_commodity_future_transaction_record.buy_date = row[3]
                optimize2_commodity_future_transaction_record.buy_price = row[4]
                optimize2_commodity_future_transaction_record.buy_lot = row[5]
                optimize2_commodity_future_transaction_record.sell_date = row[6]
                optimize2_commodity_future_transaction_record.sell_price = row[7]
                optimize2_commodity_future_transaction_record.sell_lot = row[8]
                optimize2_commodity_future_transaction_record.direction = row[9]
                optimize2_commodity_future_transaction_record.profit_and_loss = row[10]
                optimize2_commodity_future_transaction_record.profit_and_loss_rate = row[11]
                optimize2_commodity_future_transaction_record.open_commission = row[12]
                optimize2_commodity_future_transaction_record.close_commission = row[13]
                optimize2_commodity_future_transaction_record.k_line_movement_pattern = row[14]
                optimize2_commodity_future_transaction_record_list.append(optimize2_commodity_future_transaction_record)
            return optimize2_commodity_future_transaction_record_list
        else:
            None

    def update_when_close_position(self, direction: int, transaction_date: str, close_price: float,
                                   commodity_future_info: CommodityFutureInfo, lot: int, close_position_reason: int, id: int):
        """
        更新optimize2_c_f_transact_record表的sell_date/buy_date、sell_price/buy_price、ell_lot/buy_lot、profit_and_loss和profit_and_loss_rate字段
        """

        with connection.cursor() as cursor:
            sql: str = None
            if direction == Direction.Up:
                sql = "update optimize2_c_f_transact_record t " \
                      "set t.sell_date = to_date({}, 'yyyy-mm-dd'), t.sell_price  = {}, t.sell_lot = t.buy_lot, " \
                      "t.open_commission = {}, " \
                      "t.close_commission = {}, " \
                      "t.close_position_reason = {}" \
                      "where t.id_ = {}".format(transaction_date, close_price,
                                                FeeUtil.calculate_open_commodity_future_fee(close_price, commodity_future_info, lot),
                                                FeeUtil.calculate_close_old_commodity_future_fee(close_price, commodity_future_info, lot),
                                                close_position_reason,
                                                id)
                cursor.execute(sql)

                sql = "update optimize2_c_f_transact_record t " \
                      "set t.profit_and_loss = ROUND((t.sell_price - t.buy_price) * t.buy_lot * {}, 4), " \
                      "t.profit_and_loss_rate = ((t.sell_price - t.buy_price) * t.buy_lot * {}) / (t.buy_price * t.buy_lot * {}) * 100 " \
                      "where t.id_ = {}".format(commodity_future_info.transaction_multiplier,
                                                commodity_future_info.transaction_multiplier, commodity_future_info.transaction_multiplier,
                                                id)
                cursor.execute(sql)

            if direction == Direction.Down:
                sql = "update optimize2_c_f_transact_record t " \
                      "set t.buy_date = to_date({}, 'yyyy-mm-dd'), t.buy_price  = {}, t.buy_lot = t.sell_lot, " \
                      "t.open_commission = {}, " \
                      "t.close_commission = {}, " \
                      "t.close_position_reason = {} " \
                      "where t.id_ = {}".format(transaction_date, close_price,
                                                FeeUtil.calculate_open_commodity_future_fee(close_price, commodity_future_info, lot),
                                                FeeUtil.calculate_close_old_commodity_future_fee(close_price, commodity_future_info, lot),
                                                close_position_reason, id)
                cursor.execute(sql)

                sql = "update optimize2_c_f_transact_record t " \
                      "set t.profit_and_loss = -ROUND((t.buy_price - t.sell_price) * t.sell_lot * {}, 4), " \
                      "t.profit_and_loss_rate = -((t.buy_price - t.sell_price) * t.sell_lot * {}) / (t.sell_price * t.sell_lot * {}) * 100 " \
                      "where t.id_ = {}".format(commodity_future_info.transaction_multiplier,
                                                commodity_future_info.transaction_multiplier,  commodity_future_info.transaction_multiplier,
                                                id)
                cursor.execute(sql)